package org.zjvis.datascience.common.sql;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import org.zjvis.datascience.common.model.stat.ColumnConstant;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.vo.column.ColumnQueryVO;

import java.util.List;
import java.util.Map;
import java.util.Set;

import static org.zjvis.datascience.common.constant.DatabaseConstant.GP_LIMIT_OFFSET_SQL;

/**
 * @description : 字段转换SQL帮助类
 * @date 2021-10-14
 */
public class TransFormSqlHelper {
    private final Map<String, Integer> columnTypes;
    private final JSONObject data;

    public TransFormSqlHelper(Map<String, Integer> columnTypes, JSONObject data) {
        this.columnTypes = columnTypes;
        this.data = data;
    }

    /**
     * 生成预览用的sql
     */
    public static String initPreviewSql(Map<String, Integer> columnTypes, ColumnQueryVO vo,
                                        String orderBy, int pageSize, int offset) {
        TransFormSqlHelper sqlHelper = new TransFormSqlHelper(columnTypes, vo.getData());
        JSONObject data = vo.getData();
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String table = vo.getTable();
        //字段拆分的预览，展示拆分后的结果
        if ("TRANSFORM_SPLIT".equals(data.getString("action"))) {
            String sql = DataCleanSqlHelper.initSql(columnTypes, data, 1L);
            return sql.substring(sql.indexOf("AS") + 2) + " " + sqlHelper.initWhereSql()
                    + String.format(GP_LIMIT_OFFSET_SQL, pageSize, offset);
        }

        switch (transformType) {
            case ColumnConstant.TRANSFORM_REMOVE:
                table = sqlHelper.selectRepeat(orderBy, table);
                break;
        }
        String where = sqlHelper.initWhereSql();

        String sql = String.format("SELECT * FROM %s %s LIMIT %s OFFSET %s",
                table, where, pageSize, offset);
        return sql;
    }

    /**
     * 生成筛选的sql
     */
    public String initWhereSql() {
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String where = "";
        switch (transformType) {
            case ColumnConstant.SELECT_NULL:
                where = selectNull();
                break;
            case ColumnConstant.TRANSFORM_REMOVE:
                where = "WHERE \"_row_num_\" = 2";
                break;
            case ColumnConstant.WORD_POSITION:
                where = selectWordPosition();
                break;
            case ColumnConstant.AFTER_BEFORE:
                where = selectAfterBefore();
                break;
            case ColumnConstant.POSITION:
                where = selectPosition();
                break;
        }
        return where;
    }

    /**
     * 刷选光标位置的预览
     */
    private String selectPosition() {
        int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
        String col = SqlUtil.formatPGSqlColName(data.getString(ColumnConstant.COL));
        return String.format("WHERE char_length(%s) >= %d", col, cursorEnd);
    }

    /**
     * 刷选A单词之前B单词之后内容的预览
     */
    private String selectAfterBefore() {
        String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
        String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
        String col = SqlUtil.formatPGSqlColName(data.getString(ColumnConstant.COL));
        String brushValue = DataCleanSqlHelper.getBrushValueSql(after, before, col);
        return "WHERE " + brushValue + " is not null";
    }

    /**
     * 刷选某个位置的单词的预览
     */
    private String selectWordPosition() {
        int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
        String col = SqlUtil.formatPGSqlColName(data.getString(ColumnConstant.COL));
        return String.format("WHERE split_part(%s, ' ', %d) != ''", col, wordIndex);
    }

    /**
     * 查出重复的行
     *
     * @return
     */
    private String selectRepeat(String orderBy, String table) {
        JSONArray partitionCols = data.getJSONArray(ColumnConstant.PARTITION_COLS);
        List<String> cols = SqlUtil.formatPGSqlCols(partitionCols.toJavaList(String.class));
        String partitionBy = Joiner.on(",").join(cols);
        return String.format("(select row_number() over(partition by %s order by %s) as \"_row_num_\",* from %s) a"
                , partitionBy, orderBy, table);
    }

    /**
     * 查出含缺失值的行
     *
     * @return
     */
    private String selectNull() {
        Set<String> cols = columnTypes.keySet();
        List<String> colList = Lists.newArrayList();
        String sql;
        for (String colName : cols) {
            sql = "\"" + colName + "\" is null";
            if (SqlUtil.isString(columnTypes.get(colName))) {
                sql = sql + " or \"" + colName + "\" = ''";
            }
            colList.add(sql);
        }
        return "WHERE " + Joiner.on(" OR ").join(colList);
    }
}
